package com.cheney.demo.data;

import org.apache.poi.ss.usermodel.*;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import java.io.*;
import java.util.*;

//用Java，实现如图的转换，输入文件来自D:/tmp/import1.xlsx，输出文件保存在D:/tmp/export1.xlsx
public class KK差异_01 {
    public static void main(String[] args) throws IOException {
        // 读取输入Excel文件
        List<String[]> inputData = readExcelFile("D:/tmp/import1.xlsx");
        
        
        // 修改为使用Map来存储元素及其出现次数
        Map<String, Integer> aMap = new HashMap<>();
        Map<String, Integer> bMap = new HashMap<>();
        
        // 跳过标题行，统计每个元素出现的次数
        for (int i = 1; i < inputData.size(); i++) {
            String[] row = inputData.get(i);
            if (!row[0].trim().isEmpty()) {
                aMap.merge(row[0].trim(), 1, Integer::sum);
            }
            if (!row[1].trim().isEmpty()) {
                bMap.merge(row[1].trim(), 1, Integer::sum);
            }
        }
        
        // 计算交集和各自独有的元素（连同它们的出现次数）
        Map<String, Integer> intersection = new HashMap<>();
        Map<String, Integer> onlyInA = new HashMap<>();
        Map<String, Integer> onlyInB = new HashMap<>();
        
        for (Map.Entry<String, Integer> entry : aMap.entrySet()) {
            if (bMap.containsKey(entry.getKey())) {
                intersection.put(entry.getKey(), entry.getValue());
            } else {
                onlyInA.put(entry.getKey(), entry.getValue());
            }
        }
        
        for (Map.Entry<String, Integer> entry : bMap.entrySet()) {
            if (!aMap.containsKey(entry.getKey())) {
                onlyInB.put(entry.getKey(), entry.getValue());
            }
        }
        
        // 创建新的工作簿
        Workbook workbook = new XSSFWorkbook();
        Sheet sheet = workbook.createSheet("Result");
        
        // 写入标题行
        Row headerRow = sheet.createRow(0);
        headerRow.createCell(0).setCellValue("只在a");
        headerRow.createCell(1).setCellValue("重复数");
        headerRow.createCell(2).setCellValue("只在b");
        headerRow.createCell(3).setCellValue("重复数");
        headerRow.createCell(4).setCellValue("交叉");
        headerRow.createCell(5).setCellValue("a中重复数");
        headerRow.createCell(6).setCellValue("b中重复数");
        
        // 写入数据
        int maxRows = Math.max(Math.max(onlyInA.size(), onlyInB.size()), intersection.size());
        Iterator<Map.Entry<String, Integer>> iterA = onlyInA.entrySet().iterator();
        Iterator<Map.Entry<String, Integer>> iterB = onlyInB.entrySet().iterator();
        Iterator<Map.Entry<String, Integer>> iterIntersection = intersection.entrySet().iterator();
        
        for (int i = 0; i < maxRows; i++) {
            Row row = sheet.createRow(i + 1);
            
            if (iterA.hasNext()) {
                Map.Entry<String, Integer> entry = iterA.next();
                row.createCell(0).setCellValue(entry.getKey());
                row.createCell(1).setCellValue(entry.getValue());
            }
            if (iterB.hasNext()) {
                Map.Entry<String, Integer> entry = iterB.next();
                row.createCell(2).setCellValue(entry.getKey());
                row.createCell(3).setCellValue(entry.getValue());
            }
            if (iterIntersection.hasNext()) {
                Map.Entry<String, Integer> entry = iterIntersection.next();
                String key = entry.getKey();
                row.createCell(4).setCellValue(key);
                row.createCell(5).setCellValue(aMap.get(key));  // a中的重复数
                row.createCell(6).setCellValue(bMap.get(key));  // b中的重复数
            }
        }
        
        // 自动调整列宽
        for (int i = 0; i < 7; i++) {  // 修改为7列
            sheet.autoSizeColumn(i);
        }
        
        // 保存文件
        try (FileOutputStream fileOut = new FileOutputStream("D:/tmp/export1.xlsx")) {
            workbook.write(fileOut);
        }
        workbook.close();
    }
    
    private static List<String[]> readExcelFile(String filePath) throws IOException {
        List<String[]> data = new ArrayList<>();
        try (FileInputStream inputStream = new FileInputStream(new File(filePath));
             Workbook workbook = new XSSFWorkbook(inputStream)) {
            Sheet sheet = workbook.getSheetAt(0);
            for (Row row : sheet) {
                String[] rowData = new String[2];
                for (int i = 0; i < 2; i++) {
                    Cell cell = row.getCell(i, Row.MissingCellPolicy.CREATE_NULL_AS_BLANK);
                    if (cell.getCellType() == CellType.BLANK) {
                        rowData[i] = "";
                    } else if (cell.getCellType() == CellType.NUMERIC) {
                        rowData[i] = String.format("%.0f", cell.getNumericCellValue());
                    } else {
                        rowData[i] = cell.toString().trim();
                    }
                }
                data.add(rowData);
            }
        }
        return data;
    }
}
